﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BookingSystemModel;
using System.Data.OracleClient;

namespace BookingSystemDAL
{
    public class FlightDAL
    {
        public FlightDAL()
        {

        }
        public  DataSet GetAllFlight()
        {
            string sql = "select * from FlightTable";
            return OracleHelper.ExecuteDataSet(sql);
        }

        public  DataSet GetFlightByFlightNumber(string FlightNumber)
        {
            string sql = "select *from FlightTable where FlightNumber=" +"'"+ FlightNumber+"'";
            return OracleHelper.ExecuteDataSet(sql);
        }
        //删除航班
        public int DeleteFlight(string Id)
        {
            string sql = "delete from FlightTable where FlightID in("+Id+")";
            return OracleHelper.ExecuteNonQuery(sql);
        }
        //增加航班
        public int AddFlight(Flight flight)
        {
            int rows = 0;
            string sql = "insert into FlightTable(FlightNumber,FlightType,FirstSeat,FirstSeatPrice,BusinessSeat,BusinessSeatPrice,EconomicSeat,EconomicSeatPrice,FromAddress,ToAddress,FromDate,ToDate,FromTime)"
                +
                                                     "values(:FlightNumber,:FlightType,:FirstSeat,:FirstSeatPrice,:BusinessSeat,:BusinessSeatPrice,:EconomicSeat,:EconomicSeatPrice,:FromAddress,:ToAddress,:FromDate,:ToDate,:FromTime)";
            OracleParameter[] param =
            {
                new OracleParameter(":FlightNumber",flight.FlightNumber),
                new OracleParameter(":FlightType",flight.FlightType),
                new OracleParameter(":FirstSeat",flight.FirstSeat),
                new OracleParameter(":FirstSeatPrice",flight.FirstSeatPrice),
                new OracleParameter(":BusinessSeat",flight.BusinessSeat),
                new OracleParameter(":BusinessSeatPrice",flight.BusinessSeatPrice),
                new OracleParameter(":EconomicSeat",flight.EconomicSeat ),
                new OracleParameter(":EconomicSeatPrice",flight.EconomicSeatPrice),
                new OracleParameter(":FromAddress",flight.FromAddress),
                new OracleParameter(":ToAddress",flight.ToAddress),
                new OracleParameter(":FromDate",flight.FromDate),
                new OracleParameter(":ToDate",flight.ToDate),
                new OracleParameter(":FromTime",flight.FromTime)
            };
            try
            {
                 rows= OracleHelper.ExecuteNonQuery(sql,param);
            }
            catch (System.Exception ex)
            {
            	throw(ex);
            }

            return rows;  
        }
        //根据出发地，目的地，出发时间查询航班
        public DataSet GetDCFlight(string FromAddress, string ToAddress, string FromDate)
        {
            string sql = "select * from FlightTable where FromAddress=" + "'" + FromAddress + "'" + "and ToAddress=" + "'" + ToAddress + "'" + "and FromDate=" + "'" + FromDate + "'";
            try
            {
                return OracleHelper.ExecuteDataSet(sql);
            }
            catch (System.Exception ex)
            {
                throw (ex);
            }
        }
    }
}
